import com.envy.entity.Student;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class TestMethod {
    private JdbcTemplate jdbcTemplate;
    {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    }

    public void TestExecute(){
        jdbcTemplate.execute("create table testcourse(id int, name varchar(20))");
    }

    public void TestUpdate1(){
        String sql = "insert into student(name,sex)values(?,?)";
        jdbcTemplate.update(sql,new Object[]{"小明","男"});
    }


    public void TestUpdate2(){
        String sql = "update student set sex=? where id =?";
        jdbcTemplate.update(sql,"男",1001);
    }

    public void TestBatchUpdate1(){
        String [] sqls = {
           "insert into student(name,sex)values('小白','男')",
           "insert into student(name,sex)values('小花','女')",
           "update student set sex='女' where id =1002"
        };
        jdbcTemplate.batchUpdate(sqls);
    }

    public void TestBatchUpdate2(){
        String sql = "insert into selection(student,course)values(?,?)";
        List<Object[]> list = new ArrayList<Object[]>();
        list.add(new Object[]{1002,1001});
        list.add(new Object[]{1002,1003});
        jdbcTemplate.batchUpdate(sql,list);
    }

    public void querySimpleOne(){
        String sql = "select count(*) from student";
        int count = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(count);
    }

    public void querySimpleTwo(){
        String sql = "select name from student where sex=?";
        List<String> names = jdbcTemplate.queryForList(sql,String.class,"男");
        System.out.println(names);
    }

    public void queryComplexOne(){
        String sql = "select * from student where id=?";
        Map<String,Object> numbers = jdbcTemplate.queryForMap(sql,1001);
        System.out.println(numbers);
    }

    public void queryComplexTwo(){
        String sql = "select * from student ";
        List<Map<String,Object>> listNums = jdbcTemplate.queryForList(sql);
        System.out.println(listNums);
    }


    //实现了RowMapper<com.envy.entity.Student>接口的StudentRowMapper类
    private class StudentRowMapper implements RowMapper<Student>{
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setSex(resultSet.getString("sex"));
            student.setBorn(resultSet.getDate("born"));
            return student;
        }
    }

    public void queryComplexEntityOne(){
        String sql = "select * from student where id=? ";
        Student student = jdbcTemplate.queryForObject(sql, new StudentRowMapper());
        System.out.println(student);
    }


    public void queryComplexEntityTwo(){
        String sql = "select * from student";
        List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper());
        System.out.println(students);
    }


}
